USE [ConversionDB]
GO
/****** Object:  StoredProcedure [dbo].[SyncDataFromStudentDBtoRafiki]    Script Date: 03/30/2016 17:26:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Taylor Chase
-- Create date: March 11, 2016
-- Description:	Sync Student Database with RafikiDB
-- =============================================
ALTER PROCEDURE [dbo].[SyncDataFromStudentDBtoRafiki] 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @JSONtoWrite TABLE (JSONvalue nvarchar(MAX),EntityID int,JSONvalue2 nvarchar(MAX))
	DECLARE @ExecResult TABLE (RequestorEntityID int,DataTableType nvarchar(100),DataID int,JSONdata nvarchar(max))
	DECLARE @CheckRequestorEntityID int,@CheckDataTableType nvarchar(100),@CheckDataID int,@CheckJSONdata nvarchar(max)
	DECLARE @JSONvalue nvarchar(MAX), @ToUpdateEntityID int, @JSONvalue2 nvarchar(MAX), @CreatedID int
	DECLARE @OpenQueryText nvarchar(MAX)

	DECLARE @WriteCursor CURSOR
	
	--**********************************************************************************************
	PRINT 'Insert new students into the database.'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,JSONvalue2)
	SELECT				'{"EntityID":"' + '-1'
					  + '","FriendlyID":"' + CAST(S.FriendlyID AS NVARCHAR)
					  + COALESCE('","FirstName":"' + S.FirstName,'')
					  + COALESCE('","MiddleName":"' + S.MiddleName,'')
					  + COALESCE('","LastName":"' + S.LastName,'')
					  + COALESCE('","PreferredName":"' + S.PreferredName,'')
					  + COALESCE('","GradYear":"' + CAST(S.GradYear AS NVARCHAR),'')
					  + '","Gender":"' + (CASE WHEN S.Gender = 0 THEN 'Female' ELSE 'Male' END)
					  + COALESCE('","Logon":"' + S.Logon,'')
					  + '","EntityType":"' + 'Person' + '"'
					  -- If the entity is to be created, it also needs to have a relationship
					  -- If they have an admissions request, use that.
					  -- If they have an enrollment record and no admissions request, use that.
					  + CASE WHEN NOT ADM.StudentID IS NULL THEN ''
					  --',"ShortRelationshipName":"' + 'APPLIED' + '","Entity2ID":"@E2ID"'
						ELSE CASE WHEN EnTerm = 1 THEN ',"Entity2ID":"10000","ShortRelationshipName":"' + 'STUDENT' + '","StartDate":"' 
							+ '08/10/' + CAST(EnYear AS nvarchar) + '","EndDate":"' + '12/15/' + CAST(EnYear AS nvarchar) + '"'
						ELSE CASE WHEN EnTerm = 2 THEN ',"Entity2ID":"10000","ShortRelationshipName":"' + 'STUDENT' + '","StartDate":"' 
							+ '12/16/' + CAST(EnYear AS nvarchar) + '","EndDate":"' + '04/10/' + CAST(EnYear+1 AS nvarchar) + '"'
						ELSE ',"Entity2ID":"10000","ShortRelationshipName":"' + 'STUDENT' + '","StartDate":"' 
							+ '04/11/' + CAST(EnYear+1 AS nvarchar) + '","EndDate":"' + '07/25/' + CAST(EnYear+1 AS nvarchar) + '"'
						END END END
					  + '}' AS JSONvalue,-1 as EntityID,
					  CASE WHEN NOT ADM.StudentID IS NULL THEN '{"EntityType":"AdmissionsRequest"'
									  + ',"EntityID":"' + '-1' + '"'
									  + ',"LDHistory":"' + CASE WHEN [Learning Difficulty History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"ADDHistory":"' + CASE WHEN [ADD History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"DisciplineHistory":"' + CASE WHEN [Discipline History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"SpecialMeds":"' + CASE WHEN [Special Medication] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"BedWetHistory":"' + CASE WHEN [Bed Wetting History] = 1 THEN 'True' ELSE 'False' END + '"'
									  -- The original question is english first language, so 1 is false and vice versa for the new system
									  + ',"ESL":"' + CASE WHEN [English First Language] = 1 THEN 'False' ELSE 'True' END + '"'
									  + ',"PersonalToilette":"' + CASE WHEN [Personal Toilette] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"MakesBed":"' + CASE WHEN [Makes Bed] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"AppFeePaid":"' + CASE WHEN [AppFeePaid] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"AdmNotes":"' + COALESCE(CAST([Admission Notes] AS NVARCHAR),'') + '"'
									  + ',"ShortRelationshipname":"' + 'APPLIEDTO' + '"'
									  + ',"Entity2ID":"' + '10000' + '"'
									  + ',"StartDate":"' + CAST(ApplicationDate AS NVARCHAR) + '"'
									  -- If the First Term and First Year are empty, set the end date to one
									  -- month after the start date
									  + CASE WHEN FYAR IS NULL OR FTAR IS NULL
											 THEN ',"EndDate":"' + CAST(DATEADD(month,1,ApplicationDate) AS NVARCHAR) + '"'
										ELSE CASE WHEN EnTerm IS NULL 
											 -- If Enrollment Term is null, this is an active application
											 THEN ''
									  -- Set the end date to the day before the first term the student was/will be here
										ELSE CASE WHEN EnTerm = 1 THEN ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('08/09/' + CAST(EnYear AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '08/09/' + CAST(EnYear AS nvarchar) END + '"'
										ELSE CASE WHEN EnTerm = 2 THEN ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '12/15/' + CAST(EnYear AS nvarchar) END + '"'
										ELSE ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '04/10/' + CAST(EnYear+1 AS nvarchar) END + '"'
										END END END END
									  + '}'
					  ELSE NULL END AS JSONvalue2
	--SELECT * 
	FROM OPENQUERY(STUDDATA97,'select StudentID AS FriendlyID,FirstName,MiddleName,LastName,PreferedName AS PreferredName,
		[Grad-Year] AS GradYear,Sex AS Gender,Logon,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR from Students') AS S 
		LEFT JOIN OPENQUERY(STUDDATA97,'SELECT StudentID,MIN(Year) AS EnYear,MIN(Term) AS EnTerm FROM [Enrollment-Term] WHERE [Type of Term] = ''Term'' AND Term > 0 GROUP BY StudentID') AS ET
			ON ET.StudentID = S.FriendlyID
		LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB @RequestorEntityID = 0,@CommandName = ''Person''') AS P
			ON CAST(S.FriendlyID AS NVARCHAR) = P.FriendlyID
			-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
		LEFT JOIN OPENQUERY(STUDDATA97,'SELECT SAI.* FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL') AS ADM
			ON S.FriendlyID = ADM.StudentID
	WHERE (P.EntityID IS NULL AND ((S.GradYear > 2016 AND ET.EnYear IS NULL) OR NOT ET.EnYear IS NULL))
	--**********************************************************************************************
	PRINT 'Check existing students and update as needed.'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,JSONvalue2)
	SELECT				'{"EntityID":"' + COALESCE(CAST(P.EntityID AS NVARCHAR),'-1') 
					  + '","FriendlyID":"' + CAST(S.FriendlyID AS NVARCHAR)
					  + COALESCE('","FirstName":"' + S.FirstName,'')
					  + COALESCE('","MiddleName":"' + S.MiddleName,'')
					  + COALESCE('","LastName":"' + S.LastName,'')
					  + COALESCE('","PreferredName":"' + S.PreferredName,'')
					  + '","GradYear":"' + CAST(S.GradYear AS NVARCHAR)
					  + '","Gender":"' + (CASE WHEN S.Gender = 0 THEN 'Female' ELSE 'Male' END)
					  + '","Logon":"' + S.Logon
					  + '","EntityType":"' + 'Person' + '"'
					  + '}' AS JSONvalue,P.EntityID,
					  CASE WHEN NOT ADM.StudentID IS NULL THEN '{"EntityType":"AdmissionsRequest"'
									  + ',"EntityID":"' + COALESCE(CAST(AR.EntityID AS NVARCHAR),'-1') + '"'
									  + ',"LDHistory":"' + CASE WHEN [Learning Difficulty History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"ADDHistory":"' + CASE WHEN [ADD History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"DisciplineHistory":"' + CASE WHEN [Discipline History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"SpecialMeds":"' + CASE WHEN [Special Medication] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"BedWetHistory":"' + CASE WHEN [Bed Wetting History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"ESL":"' + CASE WHEN [English First Language] = 1 THEN 'False' ELSE 'True' END + '"'
									  + ',"PersonalToilette":"' + CASE WHEN [Personal Toilette] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"MakesBed":"' + CASE WHEN [Makes Bed] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"AppFeePaid":"' + CASE WHEN [AppFeePaid] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"AdmNotes":"' + COALESCE(CAST([Admission Notes] AS NVARCHAR),'') + '"'
									  + ',"ShortRelationshipname":"' + 'APPLIEDTO' + '"'
									  + ',"Entity2ID":"' + '10000' + '"'
									  + ',"StartDate":"' + CAST(ApplicationDate AS NVARCHAR) + '"'
									  -- If the First Term and First Year are empty, set the end date to one
									  -- month after the start date
									  + CASE WHEN FYAR IS NULL OR FTAR IS NULL
											 THEN ',"EndDate":"' + CAST(DATEADD(month,1,ApplicationDate) AS NVARCHAR) + '"'
										ELSE CASE WHEN EnTerm IS NULL 
											 -- If Enrollment Term is null, this is an active application
											 THEN ''
									  -- Set the end date to the day before the first term the student was/will be here
										ELSE CASE WHEN EnTerm = 1 THEN ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('08/09/' + CAST(EnYear AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '08/09/' + CAST(EnYear AS nvarchar) END + '"'
										ELSE CASE WHEN EnTerm = 2 THEN ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '12/15/' + CAST(EnYear AS nvarchar) END + '"'
										ELSE ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '04/10/' + CAST(EnYear+1 AS nvarchar) END + '"'
										END END END END
									  + '}'
					  ELSE NULL END AS JSONvalue2
	--SELECT * 
	FROM OPENQUERY(STUDDATA97,'select StudentID AS FriendlyID,FirstName,MiddleName,LastName,PreferedName AS PreferredName,
		[Grad-Year] AS GradYear,Sex AS Gender,Logon,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR from Students') AS S 
		INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB @RequestorEntityID = 0,@CommandName = ''Person''') AS P
			ON CAST(S.FriendlyID AS NVARCHAR) = P.FriendlyID
		LEFT JOIN OPENQUERY(STUDDATA97,'SELECT SAI.* FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL') AS ADM
			ON S.FriendlyID = ADM.StudentID
		LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB @RequestorEntityID = 0,@CommandName = ''APPLIED''') AS App
			ON P.EntityID = App.Entity1ID
		LEFT JOIN (SELECT EntityID FROM OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB @RequestorEntityID = 0,@CommandName = ''AdmissionsRequest''') AS A) AS AR
			ON App.Entity2ID = AR.EntityID
		LEFT JOIN OPENQUERY(STUDDATA97,'SELECT StudentID,MIN(Year) AS EnYear,MIN(Term) AS EnTerm FROM [Enrollment-Term] WHERE [Type of Term] = ''Term'' AND Term > 0 GROUP BY StudentID') AS ET
			ON ET.StudentID = S.FriendlyID
	WHERE 
		(((P.FirstName IS NULL AND NOT S.FirstName IS NULL) OR P.FirstName <> S.FirstName) OR
		((P.MiddleName IS NULL AND NOT S.MiddleName IS NULL) OR P.MiddleName <> S.MiddleName) OR
		((P.LastName IS NULL AND NOT S.LastName IS NULL) OR P.LastName <> S.LastName) OR
		((P.PreferredName IS NULL AND NOT S.PreferredName IS NULL) OR P.PreferredName <> S.PreferredName) OR
		((P.GradYear IS NULL AND NOT S.GradYear IS NULL) OR P.GradYear <> S.GradYear) OR
		((P.Gender IS NULL AND NOT S.Gender IS NULL) OR NOT P.Gender LIKE (CASE WHEN S.Gender = 0 THEN 'Female' ELSE 'Male' END)) OR
		((P.Logon IS NULL AND NOT S.Logon IS NULL) OR P.Logon <> S.Logon))
		AND (NOT ADM.StudentID IS NULL AND AR.EntityID IS NULL)
	--**********************************************************************************************
	PRINT 'Writing Student Info'
	
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2 FROM @JSONtoWrite
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- If there is an admissions request, insert/update that first.
		-- AND if the student is being created, append the relationship creation logic to the student record.
		IF NOT @JSONvalue2 IS NULL BEGIN
			SET @CreatedID = NULL
			-- Retrieve the EntityID of the admissions request. (If being created it will be -1)
			SELECT @CreatedID = CAST(StringValue AS int) 
			FROM dbo.parseJSON(@JSONvalue2) WHERE [NAME] = 'EntityID'
			IF @ToUpdateEntityID IS NULL 
				PRINT 'Error when finding EntityID in string: ' + COALESCE(@JSONvalue2,'|')
			ELSE BEGIN
				DELETE FROM @ExecResult
				-- Insert or update the Admissions Request.
				INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
				EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'Entity',@JSONvalue2,@CreatedID

				SELECT @CheckJSONdata = JSONdata
				FROM @ExecResult
				-- There is an error - print it
				IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
					PRINT @CheckJSONdata
				ELSE BEGIN
					PRINT 'Good - ' + @CheckJSONdata
					SET @CreatedID = NULL
					-- Retrieve the EntityID from the result of the Insert Or Update statement.
					SELECT @CreatedID = CAST(StringValue AS int) 
					FROM dbo.parseJSON(@CheckJSONdata) WHERE [NAME] = 'EntityID'
					IF @CreatedID IS NULL 
						PRINT 'Error when finding EntityID in string: ' + COALESCE(@JSONvalue2,'|')
					ELSE BEGIN
						-- @CreatedID is the ID of the newly created admissions object.
						-- If the Student Entity exists (@ToUpdateEntityID <> -1) then create the APPLIED relationship
						IF @ToUpdateEntityID <> -1 BEGIN
							DELETE FROM @ExecResult
							-- Insert or update the APPLIED relationship.
							SET @OpenQueryText = '{"ShortRelationshipName":"APPLIED",
													   "Entity1ID":"' + CAST(@ToUpdateEntityID AS NVARCHAR) + '",
													   "Entity2ID":"' + CAST(@CreatedID AS NVARCHAR) + '"}'

							INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
							EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'EntityRelationship',@OpenQueryText,@CreatedID

							SELECT @CheckJSONdata = JSONdata
							FROM @ExecResult
							-- There is an error - print it
							IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
								PRINT @CheckJSONdata
							PRINT 'Good REL - ' + @CheckJSONdata
						END ELSE BEGIN
						-- If the Student Entity does not exist, append the Applied relationship to the 
						-- student JSON.  This will create the student and also create the APPLIED relationship
						-- so the student entity creation will not fail.
						-- @CreatedID is the EntityID of the Admissions object, which is necessary if
						-- the Student Entity is being created.
							-- First step is remove trailing }
							SET @JSONvalue = SUBSTRING(@JSONvalue,1,LEN(@JSONvalue)-1)
							-- Next, append Entity2ID and the APPLIED relationship name to the student record.
							SET @JSONvalue = @JSONvalue + ',"Entity2ID":"' + CAST(@CreatedID AS NVARCHAR) + '","ShortRelationshipName":"' + 'APPLIED' + '"}'
						END
					END
				END
			END
		END
	
		DELETE FROM @ExecResult
		-- Makes sure that names or notes with ' in their name will still work.
		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'Entity',@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite

	PRINT 'Finished writing student info.'
	--************************************************************************************
	-- Sync Parents
	PRINT 'Insert new parents/Staff into the database.'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"EntityID":"' + CAST(COALESCE(P.EntityID,'-1') AS NVARCHAR)
					  + '","FriendlyID":"' + CAST(OP.ParentID+10000 AS NVARCHAR)
					  + COALESCE('","FirstName":"' + OP.FirstName,'')
					  + COALESCE('","MiddleName":"' + OP.MiddleName,'')
					  + COALESCE('","LastName":"' + OP.LastName,'')
					  + COALESCE('","PreferredName":"' + OP.PreferedName,'')
					  + '","Gender":"' + (CASE WHEN OP.Sex = 0 THEN 'Female' ELSE 'Male' END)
					  + '","EntityType":"' + 'Person' + '"' +
			CASE WHEN NOT RS.EntityID IS NULL THEN ',"ShortRelationshipName":"Parent"'
												 + ',"Entity2ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"}' 
				 -- If they did not have children at RVA they are a staff member.  This 
				 -- system only supports current staff. 
				 ELSE ',"ShortRelationshipName":"Staff"' 
					+ ',"Entity2ID":"10000"' + ',"StartDate":"'+'12/16/2015'+'"' + ',"EndDate":"'+'4/10/2016'+'"}' 
			END	AS JSONvalue,-1 AS EntityID
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT PR.*,SL.Login FROM Parents AS PR 
					LEFT JOIN [Staff Logins] AS SL ON PR.ParentID = SL.ParentID') OP
	LEFT JOIN 	OPENQUERY(STUDDATA97,'SELECT MIN(StudentID) AS SID1,MotherID,FatherID FROM Students GROUP BY MotherID,FatherID') S
			ON OP.ParentID = S.MotherID OR OP.ParentID = S.FatherID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Person''') AS P
					ON P.FriendlyID = CAST(OP.ParentID+10000 AS NVARCHAR)
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Person''') AS RS
					ON RS.FriendlyID = CAST(S.SID1 AS NVARCHAR)
					-- Limits this query to people who are not in the system and are either current staff or parents of students
	WHERE P.EntityID IS NULL AND (NOT RS.EntityID IS NULL OR (OP.Staff = 1 AND OP.[Staff Active Now?] = 1 ))

	PRINT 'Update Parent and Staff info'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"EntityID":"' + CAST(COALESCE(P.EntityID,'-1') AS NVARCHAR)
					  + '","FriendlyID":"' + CAST(OP.ParentID+10000 AS NVARCHAR)
					  + COALESCE('","FirstName":"' + OP.FirstName,'')
					  + COALESCE('","MiddleName":"' + OP.MiddleName,'')
					  + COALESCE('","LastName":"' + OP.LastName,'')
					  + COALESCE('","PreferredName":"' + OP.PreferedName,'')
					  + COALESCE('","Logon":"' + OP.Login,'')
					  + '","Gender":"' + (CASE WHEN OP.Sex = 0 THEN 'Female' ELSE 'Male' END)
					  + '","EntityType":"' + 'Person' + '"}' AS JSONvalue,P.EntityID AS EntityID
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT PR.*,SL.Login FROM Parents AS PR 
					LEFT JOIN [Staff Logins] AS SL ON PR.ParentID = SL.ParentID') OP
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Person''') AS P
					ON P.FriendlyID = CAST(OP.ParentID+10000 AS NVARCHAR)
	WHERE (
		((P.FirstName IS NULL AND NOT OP.FirstName IS NULL) OR P.FirstName <> OP.FirstName) OR
		((P.MiddleName IS NULL AND NOT OP.MiddleName IS NULL) OR P.MiddleName <> OP.MiddleName) OR
		((P.LastName IS NULL AND NOT OP.LastName IS NULL) OR P.LastName <> OP.LastName) OR
		((P.PreferredName IS NULL AND NOT OP.PreferedName IS NULL) OR P.PreferredName <> OP.PreferedName) OR
		((P.Gender IS NULL AND NOT OP.Sex IS NULL) OR NOT P.Gender LIKE (CASE WHEN OP.Sex = 0 THEN 'Female' ELSE 'Male' END)) OR
		((P.Logon IS NULL AND NOT OP.Login IS NULL) OR P.Logon <> OP.Login)
	)
	
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2 FROM @JSONtoWrite
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Parent info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'Entity',@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite
	
	PRINT 'Update Parent Relationships'
	-- Create or update Father/Mother Relationships
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"ShortRelationshipName":"Father"' 
		 + ',"Entity1ID":"' + CAST(RP.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"}' AS JSONvalue,RP.EntityID AS EntityID
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT StudentID,FatherID FROM Students') S
	INNER JOIN  OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Person''') AS RS
			ON RS.FriendlyID = S.StudentID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Person''') AS RP
			ON RP.FriendlyID = CAST(FatherID + 10000 AS NVARCHAR)
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Father''') AS F
			ON F.Entity1ID = RP.EntityID AND F.Entity2ID = RS.EntityID
	WHERE F.ShortRelationshipName IS NULL

	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"ShortRelationshipName":"Mother"' 
		 + ',"Entity1ID":"' + CAST(RP.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"}' AS JSONvalue,RP.EntityID AS EntityID
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT StudentID,MotherID FROM Students') S
	INNER JOIN  OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Person''') AS RS
			ON RS.FriendlyID = S.StudentID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Person''') AS RP
			ON RP.FriendlyID = CAST(MotherID + 10000 AS NVARCHAR)
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Mother''') AS F
			ON F.Entity1ID = RP.EntityID AND F.Entity2ID = RS.EntityID
	WHERE F.ShortRelationshipName IS NULL	
	
	-- Remove generic "Parent" relationships
	
	PRINT 'Update Staff Relationships'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"ShortRelationshipName":"Staff"' 
		 + ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"10000"'
		 + ',"StartDate":"'+'12/16/2015'+'"' + ',"EndDate":"'+'4/10/2016'+'"}' AS JSONvalue,P.EntityID AS EntityID
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT PR.*,SL.Login FROM Parents AS PR 
					LEFT JOIN [Staff Logins] AS SL ON PR.ParentID = SL.ParentID
					WHERE Staff = 1') OP
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Person''') AS P
					ON P.FriendlyID = CAST(OP.ParentID+10000 AS NVARCHAR)
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Staff''') AS RS
					ON RS.Entity1ID = P.EntityID AND GETDATE() BETWEEN RS.StartDate AND RS.EndDate
					-- Limits this query to people who are not in the system and are either current staff or parents of students
	WHERE OP.[Staff Active Now?] = 1 AND RS.ShortRelationshipName IS NULL
	
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2 FROM @JSONtoWrite
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Parent info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'EntityRelationship',@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite	

	-- I only need to create, since if a student email changes, that is just a new email that should be removed in a different step
	PRINT 'Create Student Contact Info - Email'	
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"EntityID":"-1"'
		 + ',"Email":"' + E.[E-mail] + '"'
		 + ',"EntityType":"' + 'Contact' + '"'
		 + ',"EntitySubType":"' + 'Email' + '"'
		 + ',"ShortRelationshipName":"PContact"' 
		 + ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"}' AS JSONvalue,-1 AS EntityID
	--SELECT * 
	FROM
	OPENQUERY(STUDDATA97,'SELECT StudentID,[e-mail] FROM Students WHERE NOT Students.[e-mail] Is Null') E
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Person''') AS P
					ON CAST(E.StudentID AS NVARCHAR) = P.FriendlyID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''PContact''') AS PC
					ON P.EntityID = PC.Entity1ID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Contact.Email''') AS CE
					ON CE.EntityID = PC.Entity2ID AND CE.Email = E.[e-mail]
	WHERE CE.EntityID IS NULL
	
	-- Remove student emails that are not in the old system	
	
	-- Add Parent Logons
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"EntityID":"-1"'
		 + ',"Email":"' + E.[E-mail] + '"'
		 + ',"EntityType":"' + 'Contact' + '"'
		 + ',"EntitySubType":"' + 'Email' + '"'
		 + ',"ShortRelationshipName":"Logon"' 
		 + ',"Entity1ID":"' + CAST(SL.EntityID AS NVARCHAR) + '"}' AS JSONvalue,-1 AS EntityID
	--SELECT * 
	FROM
	OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail] FROM [Parent e-mail] WHERE NOT [e-mail] IS NULL AND Logon = -1') E
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Security_Logon''') AS SL
					ON CAST(E.ParentID+10000 AS NVARCHAR) = SL.FriendlyID
	WHERE SL.Logon IS NULL
	
	-- Add Parent Emails
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"EntityID":"-1"'
		 + ',"Email":"' + E.[E-mail] + '"'
		 + ',"EntityType":"' + 'Contact' + '"'
		 + ',"EntitySubType":"' + 'Email' + '"'
		 + ',"ShortRelationshipName":"PContact"' 
		 + ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"}' AS JSONvalue,-1 AS EntityID
	--SELECT * 
	FROM
	OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail] FROM [Parent e-mail] WHERE NOT [e-mail] IS NULL AND Logon = -1') E
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Person''') AS P
					ON CAST(E.ParentID+10000 AS NVARCHAR) = P.FriendlyID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''PContact''') AS PC
					ON P.EntityID = PC.Entity1ID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0,
								@CommandName = ''Contact.Email''') AS CE
					ON CE.EntityID = PC.Entity2ID AND CE.Email = E.[e-mail]
	WHERE CE.EntityID IS NULL
	
	-- Remove parent emails that are not in the old system

	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2 FROM @JSONtoWrite
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Parent info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'Entity',@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite	
	
	PRINT 'Update Application Info'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"EntityType":"AdmissionsRequest"'
			  + ',"EntityID":"' + CAST(AR.EntityID AS NVARCHAR) + '"'
			  + ',"LDHistory":"' + CASE WHEN [Learning Difficulty History] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"ADDHistory":"' + CASE WHEN [ADD History] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"DisciplineHistory":"' + CASE WHEN [Discipline History] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"SpecialMeds":"' + CASE WHEN [Special Medication] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"BedWetHistory":"' + CASE WHEN [Bed Wetting History] = 1 THEN 'True' ELSE 'False' END + '"'
			  -- The original question is english first language, so 1 is false and vice versa for the new system
			  + ',"ESL":"' + CASE WHEN [English First Language] = 1 THEN 'False' ELSE 'True' END + '"'
			  + ',"PersonalToilette":"' + CASE WHEN [Personal Toilette] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"MakesBed":"' + CASE WHEN [Makes Bed] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"AppFeePaid":"' + CASE WHEN ADM.[AppFeePaid] = 1 THEN 'True' ELSE 'False' END + '"'
			  + ',"AdmNotes":"' + COALESCE(CAST([Admission Notes] AS NVARCHAR),'') + '"}' AS JSONvalue,
			  AR.EntityID AS EntityID
	--SELECT *
	FROM 
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''NOSQL'',
								@CommandName = ''SELECT AR.*,P.FriendlyID,AT.* FROM {AdmissionsRequest AS AR} 
									  INNER JOIN {APPLIED AP} ON AR.EntityID = AP.Entity2ID
									  INNER JOIN {Person AS P} ON P.EntityID = AP.Entity1ID
									  INNER JOIN {APPLIEDTO AS AT} ON AT.Entity1ID = AR.EntityID''') AS AR
									  ON ADM.StudentID = AR.FriendlyID
	WHERE (
		((AR.LDHistory IS NULL AND NOT ADM.[Learning Difficulty History] IS NULL) 
			OR (NOT AR.LDHistory LIKE CASE WHEN [Learning Difficulty History] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.ADDHistory IS NULL AND NOT ADM.[ADD History] IS NULL) 
			OR (NOT AR.ADDHistory LIKE CASE WHEN [ADD History] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.DisciplineHistory IS NULL AND NOT ADM.[Discipline History] IS NULL) 
			OR (NOT AR.DisciplineHistory LIKE CASE WHEN [Discipline History] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.SpecialMeds IS NULL AND NOT ADM.[Special Medication] IS NULL) 
			OR (NOT AR.SpecialMeds LIKE CASE WHEN [Special Medication] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.BedWetHistory IS NULL AND NOT ADM.[Bed Wetting History] IS NULL) 
			OR (NOT AR.BedWetHistory LIKE CASE WHEN ADM.[Bed Wetting History] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.ESL IS NULL AND NOT ADM.[English First Language] IS NULL) 
			OR (NOT AR.ESL LIKE CASE WHEN [English First Language] = 1 THEN 'False' ELSE 'True' END)) OR
		((AR.PersonalToilette IS NULL AND NOT ADM.[Personal Toilette] IS NULL) 
			OR (NOT AR.PersonalToilette LIKE CASE WHEN ADM.[Personal Toilette] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.MakesBed IS NULL AND NOT ADM.[Makes Bed] IS NULL) 
			OR (NOT AR.MakesBed LIKE CASE WHEN ADM.[Makes Bed] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.AppFeePaid IS NULL AND NOT ADM.[AppFeePaid] IS NULL) 
			OR (NOT AR.AppFeePaid LIKE CASE WHEN ADM.[AppFeePaid] = 1 THEN 'True' ELSE 'False' END)) OR
		((AR.AdmNotes IS NULL AND NOT ADM.[Admission Notes] IS NULL) 
			OR (NOT AR.AdmNotes LIKE COALESCE(CAST([Admission Notes] AS NVARCHAR),''))) 
	)

	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2 FROM @JSONtoWrite
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Parent info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'Entity',@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite	

	PRINT 'Update Application Date'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"ShortRelationshipname":"' + 'APPLIEDTO' + '"'
			  + ',"Entity1ID":"' + CAST(AR.EntityID AS NVARCHAR) + '"'
			  + ',"Entity2ID":"' + '10000' + '"'
			  + ',"StartDate":"' + CAST(ApplicationDate AS NVARCHAR) + '"'
			  -- If the First Term and First Year are empty, set the end date to one
			  -- month after the start date
			  + CASE WHEN FYAR IS NULL OR FTAR IS NULL
					 THEN ',"EndDate":"' + CAST(DATEADD(month,1,ApplicationDate) AS NVARCHAR) + '"'
				ELSE CASE WHEN EnTerm IS NULL 
					 -- If Enrollment Term is null, this is an active application
					 THEN ''
			  -- Set the end date to the day before the first term the student was/will be here
				ELSE CASE WHEN EnTerm = 1 THEN ',"EndDate":"' 
					+ CASE WHEN ApplicationDate > CAST('08/09/' + CAST(EnYear AS nvarchar) AS DateTime)
							THEN CAST(ApplicationDate AS NVARCHAR) ELSE '08/09/' + CAST(EnYear AS nvarchar) END + '"'
				ELSE CASE WHEN EnTerm = 2 THEN ',"EndDate":"' 
					+ CASE WHEN ApplicationDate > CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime)
							THEN CAST(ApplicationDate AS NVARCHAR) ELSE '12/15/' + CAST(EnYear AS nvarchar) END + '"'
				ELSE ',"EndDate":"' 
					+ CASE WHEN ApplicationDate > CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
							THEN CAST(ApplicationDate AS NVARCHAR) ELSE '04/10/' + CAST(EnYear+1 AS nvarchar) END + '"'
				END END END END
			  + '}' AS JSONvalue,AR.EntityID AS EntityID
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''NOSQL'',
								@CommandName = ''SELECT AR.*,P.FriendlyID,AT.* FROM {AdmissionsRequest AS AR} 
									  INNER JOIN {APPLIED AP} ON AR.EntityID = AP.Entity2ID
									  INNER JOIN {Person AS P} ON P.EntityID = AP.Entity1ID
									  INNER JOIN {APPLIEDTO AS AT} ON AT.Entity1ID = AR.EntityID''') AS AR
									  ON ADM.StudentID = AR.FriendlyID
	LEFT JOIN OPENQUERY(STUDDATA97,'SELECT StudentID,MIN(Year) AS EnYear,MIN(Term) AS EnTerm FROM [Enrollment-Term] WHERE [Type of Term] = ''Term'' AND Term > 0 GROUP BY StudentID') AS ET
									  ON ET.StudentID = ADM.StudentID
	WHERE 
		AR.StartDate <> ApplicationDate
		OR (NOT AR.EndDate IS NULL AND EnTerm IS NULL AND NOT FYAR IS NULL AND NOT FTAR IS NULL)
		OR
		AR.EndDate <> 
		(CASE WHEN FYAR IS NULL OR FTAR IS NULL
					 THEN DATEADD(month,1,ApplicationDate)
		-- Set the end date to the day before the first term the student was/will be here
		ELSE CASE WHEN EnTerm = 1 THEN 
			CASE WHEN ApplicationDate > CAST('08/09/' + CAST(EnYear AS nvarchar) AS DateTime)
					THEN ApplicationDate ELSE CAST('08/09/' + CAST(EnYear AS nvarchar) AS DateTime) END
		ELSE CASE WHEN EnTerm = 2 THEN 
			CASE WHEN ApplicationDate > CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime)
					THEN ApplicationDate ELSE CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime) END
		ELSE 
			CASE WHEN ApplicationDate > CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
					THEN ApplicationDate ELSE CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime) END
		END END END)
	
	PRINT 'Update/Insert Enrollments'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"ShortRelationshipName":"STUDENT","Entity2ID":"10000"'
			+ ',"Entity1ID":"' + CAST(AR.EntityID AS NVARCHAR) + '"'
			+ CASE WHEN EnTerm = 1 THEN ',"StartDate":"' 
						+ '08/10/' + CAST(EnYear AS nvarchar) + '","EndDate":"' + '12/15/' + CAST(EnYear AS nvarchar) + '"'
					ELSE CASE WHEN EnTerm = 2 THEN ',"StartDate":"' 
						+ '12/16/' + CAST(EnYear AS nvarchar) + '","EndDate":"' + '04/10/' + CAST(EnYear+1 AS nvarchar) + '"'
					ELSE ',"StartDate":"' 
						+ '04/11/' + CAST(EnYear+1 AS nvarchar) + '","EndDate":"' + '07/25/' + CAST(EnYear+1 AS nvarchar) + '"'
					END END
			
			+'}' AS JSONvalue,AR.EntityID AS EntityID
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT StudentID,Year AS EnYear,Term AS EnTerm 
									 FROM [Enrollment-Term] WHERE [Type of Term] = ''Term'' 
									 AND Term > 0') AS ET
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Person''') AS AR
									  ON ET.StudentID = AR.FriendlyID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Student''') AS ST
									  ON ST.Entity1ID = AR.EntityID AND (
		  ST.Startdate = CASE WHEN EnTerm = 1 THEN CAST('08/10/' + CAST(EnYear AS nvarchar) AS DateTime)
					ELSE CASE WHEN EnTerm = 2 THEN CAST('12/16/' + CAST(EnYear AS nvarchar) AS DateTime)
											  ELSE CAST('04/11/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
					END END
		 AND ST.EndDate = CASE WHEN EnTerm = 1 THEN CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime)
					ELSE CASE WHEN EnTerm = 2 THEN CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
											  ELSE CAST('07/25/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
					END END)
	WHERE ST.Entity1ID IS NULL

	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2 FROM @JSONtoWrite
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Enrollment/Application info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'EntityRelationship',@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite	

	PRINT 'Admissions Process Steps - Accepted'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16699"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(ADM.AcceptedDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.Accepted = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Person''') AS P
									  ON ADM.StudentID = P.FriendlyID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Applied''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''ADMCOMP''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16699
	WHERE AC.ShortRelationshipName IS NULL
	

	PRINT 'Admissions Process Steps - ParentConfirmation'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16702"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(ADM.ParentConfirmationDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.ParentConfirmation = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Person''') AS P
									  ON ADM.StudentID = P.FriendlyID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Applied''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''ADMCOMP''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16702
	WHERE AC.ShortRelationshipName IS NULL

	PRINT 'Admissions Process Steps - DepositPaid'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16703"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(ADM.DepositPaidDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.DepositPaid = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Person''') AS P
									  ON ADM.StudentID = P.FriendlyID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Applied''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''ADMCOMP''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16703
	WHERE AC.ShortRelationshipName IS NULL

	PRINT 'Admissions Process Steps - ITComplete'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16704"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(ADM.ITCompleteDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.ITComplete = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Person''') AS P
									  ON ADM.StudentID = P.FriendlyID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Applied''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''ADMCOMP''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16704
	WHERE AC.ShortRelationshipName IS NULL

	PRINT 'Admissions Process Steps - EnrollmentFormsComplete'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16707"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(ADM.EnrollmentFormsCompleteDate AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.EnrollmentFormsComplete = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Person''') AS P
									  ON ADM.StudentID = P.FriendlyID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Applied''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''ADMCOMP''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16707
	WHERE AC.ShortRelationshipName IS NULL

	PRINT 'Admissions Process Steps - AppFeePaid'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID)
	SELECT '{"ShortRelationshipName":"ADMCOMP","Entity2ID":"16708"'
			+ ',"Entity1ID":"' + CAST(AP.Entity2ID AS NVARCHAR) + '"'
			+ ',"StartDate":"' + CAST(COALESCE(ADM.AppFeePaidDate,ADM.ApplicationDate) AS NVARCHAR) + '"'
			+'}' AS JSONvalue,P.EntityID AS EntityID
	--SELECT * 
	FROM 
	-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
	OPENQUERY(STUDDATA97,'SELECT SAI.*,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR 
								FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON 
								SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL 
								AND SAI.AppFeePaid = -1') AS ADM
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Person''') AS P
									  ON ADM.StudentID = P.FriendlyID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''Applied''') AS AP
									  ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ReadDataFromDB 
								@RequestorEntityID = 0, @CommandType = ''View'',
								@CommandName = ''ADMCOMP''') AS AC
									  ON AP.Entity2ID = AC.Entity1ID AND AC.Entity2ID = 16708
	WHERE AC.ShortRelationshipName IS NULL
		
	SET @WriteCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT JSONvalue,EntityID,JSONvalue2 FROM @JSONtoWrite
		
	OPEN @WriteCursor
	FETCH NEXT FROM @WriteCursor
	INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Write the Enrollment/Application info:
		DELETE FROM @ExecResult

		INSERT INTO @ExecResult(RequestorEntityID,DataTableType,DataID,JSONdata)
		EXEC MBOA.RafikiDB.dbo.WriteDataToDB 0,'EntityRelationship',@JSONvalue,@ToUpdateEntityID

		SELECT @CheckJSONdata = JSONdata
		FROM @ExecResult
		-- There is an error - print it
		IF CHARINDEX('"DataError"',@CheckJSONdata) <> 0
			PRINT @CheckJSONdata
		ELSE
			PRINT CAST(@ToUpdateEntityID AS NVARCHAR) + ' - GOOD ' + @CheckJSONdata
			
		FETCH NEXT FROM @WriteCursor
		INTO @JSONvalue,@ToUpdateEntityID,@JSONvalue2
	END
	CLOSE @WriteCursor
	DEALLOCATE @WriteCursor

	DELETE FROM @ExecResult
	DELETE FROM @JSONtoWrite	
	
	PRINT 'Finished Updating Student/Staff/Parent Info'
		
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 01 Existing Students';
	--UPDATE P
	--SET P.Lname = [LastName], P.Fname = [FirstName], P.Pname = [PreferedName], P.Mname = [MiddleName], P.Gradyear = [Grad-Year], P.Citizenship1 = [Citizenship], P.Citizenship2 = S.[Citizenship2], P.Nationality1 = [Nationality], P.Gender = [Sex], P.Bdate = S.Birthday, P.Logon = S.[logon]
	----SELECT *
	--FROM OPENQUERY(STUDDATA97,'select * from Students') AS S INNER JOIN MBOA.CoreData.dbo.Persons AS P 
	--ON P.PersonID = CAST(S.StudentID AS nvarchar(20)) 
	--WHERE (S.LastName <> P.Lname OR S.FirstName <> P.Fname OR S.PreferedName <> P.Pname OR S.MiddleName <> P.Mname OR S.[Grad-Year] <> P.GradYear OR S.Citizenship <> P.Citizenship1 OR S.Citizenship2 <> P.Citizenship2 OR S.Nationality <> P.Nationality1 OR S.Sex <> P.Gender OR S.Birthday <> P.Bdate OR (P.Bdate IS NULL AND NOT S.Birthday IS NULL) OR S.Logon <> P.Logon OR (P.[Logon] IS NULL AND NOT S.Logon IS NULL));
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 02 New Students';
	--INSERT INTO MBOA.CoreData.dbo.Persons ( OldRVAID, OldIsStudent, Lname, Fname, Pname, Mname, Gradyear, Citizenship1, Citizenship2, Nationality1, Gender, Bdate, Logon, PersonID )
	--SELECT Students.StudentID, 1 AS Expr1, Students.LastName, Students.FirstName, Students.PreferedName, Students.MiddleName, Students.[Grad-Year], Students.Citizenship, Students.Citizenship2, Students.Nationality, Students.Sex, Students.Birthday, Students.logon, CAST(Students.StudentID AS nvarchar(20))
	--FROM OPENQUERY(STUDDATA97,'select * from students') Students LEFT JOIN MBOA.CoreData.dbo.Persons ON Persons.PersonID = CAST(Students.StudentID AS nvarchar(20)) 
	--WHERE Persons.PersonID IS NULL;
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 03 Existing Parents - No Bdate AcctNum';
	--UPDATE dbo_Persons
	--SET dbo_Persons.OldRVAID = [Parents].[ParentID], dbo_Persons.OldIsStudent = 0, 
	--	dbo_Persons.Fname = [FirstName], dbo_Persons.Mname = [MiddleName], 
	--	dbo_Persons.Lname = [LastName], dbo_Persons.MaidenName = [Parents].[MaidenName], 
	--	dbo_Persons.Pname = [PreferedName], dbo_Persons.Citizenship2 = [Parents].[Citizenship2], 
	--	dbo_Persons.Citizenship1 = [Citizenship], dbo_Persons.Nationality1 = [Nationality], 
	--	dbo_Persons.Title = [Parents].[Title], dbo_Persons.IsLiving = [Living], 
	--	dbo_Persons.Logon = [Login], dbo_Persons.Gender = [SEX]
	---- SELECT * 
	--FROM OPENQUERY(STUDDATA97,'SELECT * FROM Parents') Parents INNER JOIN MBOA.CoreData.dbo.Persons AS dbo_Persons 
	--ON dbo_Persons.PersonID = CAST((Parents.[ParentID]+10000) AS nvarchar(20)) 
	--	LEFT JOIN OPENQUERY(STUDDATA97,'SELECT * FROM [Staff Logins]') [Staff Logins] ON Parents.ParentID = [Staff Logins].ParentID 
	--WHERE (dbo_Persons.Fname <> [FirstName] OR dbo_Persons.Mname <> [MiddleName] OR dbo_Persons.Lname <> [LastName] OR 
	--	   dbo_Persons.MaidenName <> [Parents].[MaidenName] OR  dbo_Persons.Pname <> [PreferedName] OR 
	--	   dbo_Persons.Citizenship2 <> [Parents].[Citizenship2] OR 
	--	   dbo_Persons.Citizenship1 <> [Citizenship] OR dbo_Persons.Nationality1 <> [Nationality] OR  
	--	   dbo_Persons.Title <> [Parents].[Title]  OR dbo_Persons.IsLiving <> [Living] OR  
	--	   (NOT [Login] IS NULL AND dbo_Persons.Logon <> [Login]) OR ((NOT [Sex] IS NULL AND [Sex] <> Gender) OR Gender IS NULL));
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 04 New Parents';
	--INSERT INTO MBOA.CoreData.dbo.Persons ( OldRVAID, OldIsStudent, Fname, Mname, Lname, MaidenName, Pname, Citizenship2, Citizenship1, Nationality1, Bdate, Account_Number, Title, IsLiving, PersonID, Logon,Gender )
	--SELECT Parents.ParentID, 0 AS Expr1, Parents.FirstName, Parents.MiddleName, Parents.LastName, 
	--	Parents.MaidenName, Parents.PreferedName, Parents.Citizenship2, Parents.Citizenship, 
	--	Parents.Nationality, Parents.Birthdate, [Account Number] AS Expr2, Parents.Title, [Living], 
	--	[Parents].[ParentID]+10000 AS Expr4, [Staff Logins].Login,SEX AS Gender
	--FROM (OPENQUERY(STUDDATA97,'SELECT * FROM Parents') Parents LEFT JOIN MBOA.CoreData.dbo.Persons dbo_Persons
	--ON dbo_Persons.PersonID = CAST((Parents.[ParentID]+10000) AS nvarchar(20))  
	--LEFT JOIN OPENQUERY(STUDDATA97,'SELECT * FROM [Staff Logins]') [Staff Logins] ON Parents.ParentID = [Staff Logins].ParentID)
	--WHERE dbo_Persons.PersonID IS NULL AND FirstName <> '' AND LastName <> '';
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 05 Delete Relationships';
	--DELETE FROM MBOA.CoreData.dbo.Person_Relationships;
	---- ******************************************************************************************
	--PRINT 'Taylor - UPDATE MBOA - 06 - Add Relations - Father';
	--INSERT INTO MBOA.CoreData.dbo.Person_Relationships ( Person1ID, Person2ID, RelationshipID )
	--SELECT P1,P2,R
	--FROM OPENQUERY(STUDDATA97,'SELECT [FatherID]+10000 AS P1, Students.StudentID AS P2, 1 AS R
	--FROM Students INNER JOIN Parents ON Parents.ParentID = Students.FatherID
	--WHERE (((Students.FatherID) Is Not Null And (Students.FatherID)<>4650)) AND Parents.[FirstName] <> '''' AND Parents.[LastName] <> ''''')
	---- ******************************************************************************************
	--PRINT 'Taylor - UPDATE MBOA - 07 - Add Relations - Mother'
	--INSERT INTO MBOA.CoreData.dbo.Person_Relationships ( Person1ID, Person2ID, RelationshipID )
	--SELECT P1,P2,R 
	--FROM OPENQUERY(STUDDATA97,'SELECT [MotherID]+10000 AS P1, Students.StudentID AS P2, 2 AS R
	--FROM Students  INNER JOIN Parents ON Parents.ParentID = Students.MotherID
	--WHERE (((Students.MotherID) Is Not Null)) AND Parents.[FirstName] <> '''' AND Parents.[LastName] <> ''''');
	---- ******************************************************************************************
	--PRINT 'Taylor - UPDATE MBOA - 08 - Add Relations - PrimaryParent'
	--INSERT INTO MBOA.CoreData.dbo.Person_Relationships ( Person1ID, Person2ID, RelationshipID )
	--SELECT P1,P2,R
	--FROM OPENQUERY(STUDDATA97,'SELECT [Students.ParentID]+10000 AS P1, Students.StudentID AS P2, 3 AS R
	--FROM Students  INNER JOIN Parents ON Parents.ParentID = Students.ParentID
	--WHERE (((Students.ParentID) Is Not Null)) AND Parents.[FirstName] <> '''' AND Parents.[LastName] <> ''''');
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 09 Delete All Email Usages'
	--DELETE FROM MBOA.CoreData.dbo.Email_Usage;
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 10 Delete Emails'
	--DELETE FROM MBOA.CoreData.dbo.Email_Addresses;
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 11 New Student Emails'
	--INSERT INTO MBOA.CoreData.dbo.Email_Addresses ( PersonID, Email )
	--SELECT CAST([StudentID] AS NVARCHAR(20)), [e-mail]
	--FROM OPENQUERY(STUDDATA97,'SELECT StudentID,[e-mail] FROM Students
	--WHERE NOT Students.[e-mail] Is Null');
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 12 New Parent Emails'
	--INSERT INTO MBOA.CoreData.dbo.Email_Addresses ( PersonID, Email )
	--SELECT CAST(([ParentID]+10000) AS NVARCHAR(20)), [e-mail]
	--FROM OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail] FROM [Parent e-mail] WHERE NOT [e-mail] IS NULL')
	--WHERE CAST(([ParentID]+10000) AS NVARCHAR(20)) IN (SELECT PersonID FROM MBOA.CoreData.dbo.Persons)
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Priority'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, 'Priority' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Priority] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Priority'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - General'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, 'General' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [General] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'General'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Guidance'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, 'Guidance' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Guidance] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Guidance'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Business'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, 'Business' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Business] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Business'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Furlough'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, 'Furlough' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Furlough] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Furlough'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - 5k'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, '5k' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [5k] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = '5k'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - 10k'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, '10k' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [10k] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = '10k'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - 50k'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, '50k' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [50k] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = '50k'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Unlimited'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, 'Unlimited' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Unlimited] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Unlimited'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Attachments'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, 'Attachments' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Attachments] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Attachments'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - X-sensitive'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, 'X-sensitive' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [X-sensitive] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'X-sensitive'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Confidential'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, 'Confidential' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Confidential] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Confidential'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Taylor - Update MBOA - 13 Unsaved Email Usages - Logon'
	--INSERT INTO MBOA.CoreData.dbo.Email_Usage ( EmailID, Usage )
	--select EA.emailID, 'Logon' as VAR
	--FROM (OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail],priority FROM [parent e-mail] WHERE [Logon] = -1') PE 
	--	INNER JOIN MBOA.CoreData.dbo.Email_Addresses EA 
	--		ON EA.PersonID = cast(PE.ParentID+10000 AS VARCHAR(20)) AND PE.[e-mail] = EA.email) 
	--	LEFT OUTER JOIN MBOA.CoreData.dbo.Email_Usage EU ON EA.EmailID = EU.EmailID AND Usage = 'Logon'
	--WHERE Usage IS NULL
	---- ******************************************************************************************
	--PRINT 'Add Current Staff Enrollment'
	--INSERT INTO MBOA.CoreData.dbo.Enrollment ( PersonID, Enrollment_Year, Enrollment_Term, Enrollment_Type, IsTeacher )
	--select [ParentID]+10000,School_Year,School_Term,'Staff',1
	--from openquery(studdata97,'Select * from parents') 
	--	INNER JOIN MBOA.CoreData.dbo.Current_YearTerm ON 1=1
	-- WHERE [Staff Active Now?] = 1 AND CAST([ParentID]+10000 AS varchar(20)) NOT IN (SELECT PersonID 
	--		FROM MBOA.CoreData.dbo.Enrollment WHERE School_Year = Enrollment_Year AND School_Term = Enrollment_Term)
	---- ******************************************************************************************
	--PRINT 'Add Current Student Enrollment'
	--INSERT INTO MBOA.CoreData.dbo.Enrollment ( Enrollment_Year, Enrollment_Term, PersonID, Housing, Grade_in_School, Enrollment_Type, IsTeacher, SchoolID )
	--SELECT School_Year, Term, CAST([StudentID] AS Varchar(20)), ET.Housing, ET.[Grade in School], 'Student', 0, ET.SchoolID
	--FROM OPENQUERY(STUDDATA97,'SELECT * FROM [Enrollment-Term]') ET
	--		INNER JOIN MBOA.CoreData.dbo.Current_YearTerm CYT ON ET.Year = OLDRVAYEAR
	--		WHERE CAST(StudentID AS Varchar(20)) NOT IN (SELECT PersonID 
	--		FROM MBOA.CoreData.dbo.Enrollment WHERE School_Year = Enrollment_Year AND Term = Enrollment_Term)
	---- ******************************************************************************************
	--PRINT 'Update Current Student Enrollment'
	---- Testing data:
	----UPDATE MBOA.CoreData.dbo.Enrollment SET Grade_In_School = 11 
	----	WHERE PersonID = '5597' AND Enrollment_Year = '2015-2016' AND Enrollment_Term = '1'
	--UPDATE MBOA.CoreData.dbo.Enrollment
	--SET E.Housing = ET.Housing, E.Grade_In_School = [Grade In School]
	--FROM OPENQUERY(STUDDATA97,'SELECT * FROM [Enrollment-Term]') ET
	--		INNER JOIN MBOA.CoreData.dbo.Current_YearTerm CYT ON ET.Year = OLDRVAYEAR
	--		INNER JOIN MBOA.CoreData.dbo.Enrollment E ON CYT.School_Year = E.Enrollment_Year
	--		WHERE CAST(StudentID AS Varchar(20)) = PersonID AND ET.Term = E.Enrollment_Term
	--			AND (E.Housing <> ET.Housing OR E.Grade_In_School <> [Grade in School])
	---- ******************************************************************************************
	--PRINT 'Delete Extra Enrollments'
	---- Test Data
	----INSERT INTO MBOA.CoreData.dbo.Enrollment (PersonID,Enrollment_Year,Enrollment_Term,Enrollment_Type,IsTeacher)
	----VALUES(13424,'2015-2016',1,'Staff',1)
	--DELETE FROM MBOA.CoreData.dbo.Enrollment
	---- SELECT * FROM MBOA.COREDATA.dbo.Enrollment
	--WHERE Enrollment_Type = 'Staff'
	-- AND Enrollment_Year IN (SELECT School_Year FROM MBOA.CoreData.dbo.Current_YearTerm )
	--	AND Enrollment_Term IN (SELECT School_Term FROM MBOA.CoreData.dbo.Current_YearTerm)
	--	AND PersonID NOT IN (
	--select [ParentID]+10000
	--from openquery(studdata97,'Select * from parents') 
	-- WHERE [Staff Active Now?] = 1)


	--DELETE FROM MBOA.CoreData.dbo.Enrollment 
	--WHERE  Enrollment_Type = 'Student' 
	-- AND Enrollment_Year IN (SELECT School_Year FROM MBOA.CoreData.dbo.Current_YearTerm )
	-- AND Enrollment_Term IN (SELECT School_Term FROM MBOA.CoreData.dbo.Current_YearTerm)
	-- AND PersonID NOT IN (SELECT CAST(StudentID AS Varchar(20)) 
	--					  FROM OPENQUERY(STUDDATA97,'SELECT * FROM [Enrollment-Term]')
	--					  WHERE [YEAR] IN (SELECT OLDRVAYEAR 
	--					  FROM MBOA.CoreData.dbo.Current_YearTerm )
	--					  AND [Term] IN (SELECT School_Term 
	--					  FROM MBOA.CoreData.dbo.Current_YearTerm))
	---- Insert Peris, Esther and Peter so they can do student lookups
	--INSERT INTO MBOA.CoreData.[dbo].[Enrollment] ([PersonID],[Enrollment_Year]
	--		   ,[Enrollment_Term],[Enrollment_Type],[Grade_in_School]
	--		   ,[IsTeacher],[Housing],[SchoolID])
	--	 VALUES (99998,'2015-2016',2,'Staff',null,0,null,null);
	--INSERT INTO MBOA.CoreData.[dbo].[Enrollment] ([PersonID],[Enrollment_Year]
	--		   ,[Enrollment_Term],[Enrollment_Type],[Grade_in_School]
	--		   ,[IsTeacher],[Housing],[SchoolID])
	--	 VALUES (99999,'2015-2016',2,'Staff',null,0,null,null);
	--INSERT INTO MBOA.CoreData.[dbo].[Enrollment] ([PersonID],[Enrollment_Year]
	--		   ,[Enrollment_Term],[Enrollment_Type],[Grade_in_School]
	--		   ,[IsTeacher],[Housing],[SchoolID])
	--	 VALUES (99997,'2015-2016',2,'Staff',null,0,null,null);
	--INSERT INTO MBOA.CoreData.[dbo].[Enrollment] ([PersonID],[Enrollment_Year]
	--		   ,[Enrollment_Term],[Enrollment_Type],[Grade_in_School]
	--		   ,[IsTeacher],[Housing],[SchoolID])
	--	 VALUES (99996,'2015-2016',2,'Staff',null,0,null,null);

	---- Inserts the parent names and email info into powerschool, after removing the old records.
	--EXEC MBOA.CoreData.dbo.PS_SyncParentContacts 1
	---- Synchronizes the name and login from the student database to powerschool
	--EXEC MBOA.CoreData.dbo.PS_SyncStudentContacts
	---- Synchronizes the name, email and login from the student database to powerschool
	--EXEC MBOA.CoreData.dbo.PS_SyncTeacherContacts


    
END
